package com.github.caryyu.excel2pdf.uitil;
import com.itextpdf.text.*;
import com.itextpdf.text.Font;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.IOException;
import java.math.BigDecimal;
import java.net.MalformedURLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author laixiaoxing
 * @Description excel里面的值和样式转为pdf里面表格
 * @Date 下午11:48 2019/2/18
 */
public class PdfTableExcel {
	// ExcelObject
	protected ExcelObject excelObject;
	// excel
	protected Excel excel;
	//
	protected boolean setting = false;

	/**
	 * <p>
	 * Description: Constructor
	 * </p>
	 * 
	 * @param excelObject
	 */
	public PdfTableExcel(ExcelObject excelObject) {
		this.excelObject = excelObject;
		this.excel = excelObject.getExcel();
	}

	/**
	 * <p>
	 * Description: 获取转换过的Excel内容Table
	 * </p>
	 * 
	 * @return PdfPTable
	 * @throws BadElementException
	 * @throws MalformedURLException
	 * @throws IOException
	 */
	public PdfPTable getTable() throws BadElementException, MalformedURLException, IOException, DocumentException {
		Sheet sheet = this.excel.getSheet();
		return toParseContent(sheet,this.excel.getWorkbook());
	}

	public Map<Integer,Integer> getCaleMapForSheet(Sheet sheet,FormulaEvaluator evaluator){
		String sheetName = sheet.getSheetName();
		Map<Integer,Integer> caleMap = new HashMap<>();//key位column的索引，value为保留的精度
		int totalColumns = sheet.getLastRowNum();
		//除01表之外，金额取整、数量三位，指标2位，比例2位
		Row row = null;
		if(sheetName.indexOf("01表") != -1) {
			caleMap.put(3, 2);
			caleMap.put(4, 2);
			caleMap.put(6, 2);
			caleMap.put(7, 2);
			caleMap.put(9, 2);
		}else if(sheetName.indexOf("02表") != -1) {
			//02表金额表，默认保留0位
			row = sheet.getRow(2);//获取表头行
			for(int i = 4;i <= totalColumns;i++) {
				int cale = 0;
				Cell cell = row.getCell(i);
				if(cell == null) {
					continue;
				}
				String cellValue = getStringValue(cell,evaluator,null,null);
				if(cellValue != null && !cellValue.trim().equals("")) {
					if(!isContainChinese(cellValue)) {
						continue;//是否有中文
					}
				}else {
					continue;
				}
				if(cellValue.indexOf("%") != -1) {
					cale = 2;
				}
				if(cellValue.indexOf("涉及") != -1) {
					break;
				}
				caleMap.put(i, cale);
			}
		}else if(sheetName.indexOf("03表") != -1) {
			//03表为数量表，默认3位
			row = sheet.getRow(2);//获取表头行
			for(int i = 5;i <= totalColumns;i++) {
				int cale = 3;
				Cell cell = row.getCell(i);
				if(cell == null) {
					continue;
				}
				String cellValue = getStringValue(cell,evaluator,null,null);
				if(cellValue != null && !cellValue.trim().equals("")) {
					if(!isContainChinese(cellValue)) {
						continue;//是否有中文
					}
				}else {
					continue;
				}
				if(cellValue.indexOf("%") != -1) {
					cale = 2;
				}
				if(cellValue.indexOf("涉及") != -1) {
					break;
				}
				caleMap.put(i, cale);
			}
		}else if(sheetName.indexOf("04表") != -1 || sheetName.indexOf("05表") != -1 || sheetName.indexOf("06表") != -1) {
			//03表综合表，含有数量3位，金额0位，指标2位
			row = sheet.getRow(2);//获取表头行
			for(int i = 5;i <= totalColumns;i++) {
				int cale = 3;
				Cell cell = row.getCell(i);
				if(cell == null) {
					continue;
				}
				String cellValue = getStringValue(cell,evaluator,null,null);
				if(cellValue != null && !cellValue.trim().equals("")) {
					if(!isContainChinese(cellValue)) {
						continue;//是否有中文
					}
				}else {
					continue;
				}
				if(cellValue.indexOf("数量") != -1) {
					cale = 3;
				}else if(cellValue.indexOf("金额") != -1) {
					cale = 0;
				}else if(cellValue.indexOf("指标") != -1) {
					cale = 2;
				}if(cellValue.indexOf("涉及") != -1) {
					break;
				}
				caleMap.put(i, cale);
			}
		}else if(sheetName.indexOf("07表") != -1) {
			//07表有两种表头，这里循环判断下
			for(int j = 3; j <= 4;j++) {
				row = sheet.getRow(j);
				for(int i = 3;i <= totalColumns;i++) {
					int cale = -1;
					Cell cell = row.getCell(i);
					if(cell == null) {
						continue;
					}
					String cellValue = getStringValue(cell,evaluator,null,null);
					if(cellValue != null && !cellValue.trim().equals("")) {
						if(!isContainChinese(cellValue)) {
							continue;//是否有中文
						}
					}else {
						continue;
					}
					if(cellValue.indexOf("数量") != -1 || cellValue.indexOf("工程量") != -1) {
						cale = 3;
					}else if(cellValue.indexOf("金额") != -1) {
						cale = 0;
					}else if(cellValue.indexOf("经济") != -1 || cellValue.indexOf("单价") != -1) {
						cale = 2;
					}
					if(cale != -1) {
						caleMap.put(i, cale);
					}
				}
			}
		}else/* if(sheetName.indexOf("10表") != -1)*/ {
			//其他的全部按照乙10表标准导入,通过第三行来却确定：数量，金额和单价列
			row = sheet.getRow(2);
			int numStart = 0;//金额开始的列
			int priceStart = 0;//单价开始的列
			int sumsStart = 0;//金额开始列
			List<Integer> notEmptyList = new ArrayList<>();//不为空的列，用来确定单价，金额，和数量分别占用多少列。。。
			for(int i = 3;i <= totalColumns;i++) {
				int cale = -1;
				Cell cell = row.getCell(i);
				if(cell == null) {
					continue;
				}
				String cellValue = getStringValue(cell,evaluator,null,null);
				if(cellValue != null && !cellValue.trim().equals("")) {
					if(!isContainChinese(cellValue)) {
						continue;//是否有中文
					}
					notEmptyList.add(i);
				}else {
					continue;
				}
				if(cellValue.indexOf("工程量") != -1 || cellValue.indexOf("工程数量") != -1|| cellValue.indexOf("数量") != -1) {
					numStart = i;//可能占几列
					continue;
				}else if(cellValue.indexOf("单价") != -1) {
					priceStart = i;
					continue;
				}else if(cellValue.indexOf("金额") != -1) {
					sumsStart = i;//可能占几列
					continue;
				}else {
					//其他默认取两位
					cale = 2;
				}
				if(cale != -1) {
					caleMap.put(i, cale);
				}
			}
			for(int i = 0;i < notEmptyList.size();i++) {
				if(notEmptyList.get(i) == numStart) {
					Integer nextColumnIndex = notEmptyList.get(i+1);
					if(notEmptyList.size() > i) {
						for(int j = numStart;j < nextColumnIndex;j++) {
							caleMap.put(j, 3);
						}
					}else {
						caleMap.put(numStart, 3);
					}
				}else if(notEmptyList.get(i) == priceStart) {
					Integer nextColumnIndex = notEmptyList.get(i+1);
					if(notEmptyList.size() > i) {
						for(int j = priceStart;j < nextColumnIndex;j++) {
							caleMap.put(j, 2);
						}
					}else {
						caleMap.put(priceStart, 2);
					}
				}else if(notEmptyList.get(i) == sumsStart) {
					Integer nextColumnIndex = notEmptyList.get(i+1);
					if(notEmptyList.size() > i) {
						for(int j = sumsStart;j < nextColumnIndex;j++) {
							caleMap.put(j, 0);
						}
					}else {
						caleMap.put(sumsStart, 0);
					}
				}
			}
		}
		return caleMap;
	}
	
	 /**
     * 判断字符串中是否包含中文
     * @param str
     * 待校验字符串
     * @return 是否为中文
     * @warn 不能校验是否为中文标点符号
     */
    public static boolean isContainChinese(String str) {
        Pattern p = Pattern.compile("[\u4e00-\u9fa5]");
        Matcher m = p.matcher(str);
        if (m.find()) {
            return true;
        }
        return false;
    }
    
	/**
	 * 判读是否为纯数字
	 * @param string
	 * @return  字符串为正整数返回true 否则返回false
	 */
	public static boolean isNumber(String string){
		Pattern p=Pattern.compile("^[0-9]+$"); 
		Matcher m=p.matcher(string);
		return m.matches();		
	}
	
	protected PdfPTable toParseContent(Sheet sheet,Workbook wb)
			throws BadElementException, MalformedURLException, IOException, DocumentException {
		int rowlength = sheet.getLastRowNum() + 1;
		List<PdfPCell> cells = new ArrayList<PdfPCell>();
		float[] widths = null;
		float mw = 0;
		List<Float> columnWidths = new ArrayList<Float>();
		FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
		Map<Integer, Integer> caleMap = getCaleMapForSheet(sheet,evaluator);
		Row firstRow = sheet.getRow(0);
		CellRangeAddress firstRange = getColspanRowspanByExcel(firstRow.getRowNum(), firstRow.getCell(0).getColumnIndex());
		int firstColspan = firstRange.getLastColumn() - firstRange.getFirstColumn() + 1;//最好通过标题来把excel转化为pdf的列限制一下，否则可能会造成列错乱的问题
		for (int i = 0; i < 1000; i++)
			columnWidths.add(0f);
		int columnCount = 0;
		for (int i = 0; i < rowlength; i++) {
			Row row = sheet.getRow(i);
			if(row == null) {
				continue;
			}
			if(row.getLastCellNum() < 0) {
				continue;
			}
//			float[] cws = new float[row.getLastCellNum()];
//			int colCount = row.getLastCellNum();
			float[] cws = new float[firstColspan];//使用标题行合并的列数来决定生成pdf的内容，这样能保证不会列不会错乱
			int colCount = firstColspan;
			String prevCellVal = null;//上一个单元格的值
			for (int j = 0; j < colCount; j++) {
				if (colCount > columnCount)
					columnCount = colCount;
				Cell cell = row.getCell(j);
				// POI 產生的 Excel 可能會有 null cell
				boolean nullCell = false;
				if (cell == null) {
					nullCell = true;
					cell = row.createCell(j);
				}
				float cw = getPOIColumnWidth(cell);
				cws[cell.getColumnIndex()] = cw;
				columnWidths.set(cell.getColumnIndex(), cw);
				if (isUsed(cell.getColumnIndex(), row.getRowNum())) {
					continue;
				}
				// cell.setCellType(Cell.CELL_TYPE_STRING);
				CellRangeAddress range = getColspanRowspanByExcel(row.getRowNum(), cell.getColumnIndex());
				//
				int rowspan = 1;
				int colspan = 1;
				if (range != null) {
					rowspan = range.getLastRow() - range.getFirstRow() + 1;
					colspan = range.getLastColumn() - range.getFirstColumn() + 1;
				}
				// PDF单元格
				PdfPCell pdfpCell = new PdfPCell();

				int[] rgb = getBackgroundColor(cell.getCellStyle());
				pdfpCell.setBackgroundColor(new BaseColor(rgb[0], rgb[1], rgb[2]));
				// pdfpCell.setBackgroundColor(new
				// BaseColor(getBackgroundColorByExcel(cell.getCellStyle())));
				pdfpCell.setColspan(colspan);
				pdfpCell.setRowspan(rowspan);
				pdfpCell.setVerticalAlignment(getVAlignByExcel(cell.getCellStyle().getVerticalAlignment().getCode()));
				pdfpCell.setHorizontalAlignment(getHAlignByExcel(cell.getCellStyle().getAlignment().getCode()));
				Integer cale = caleMap.get(j);
				if(cale != null && sheet.getSheetName().indexOf("01表") != -1 && i < 12) {
					cale = 0;//01表12行上面的额数字上面的默认取0
				}
				// 设置单元格内容
				try {
					//正常可以识别的单元格的值的类型
//					pdfpCell.setPhrase(getPhrase(cell));
					pdfpCell.setPhrase(getPhrase(cell,cale,prevCellVal));
				} catch (Exception e) {
					Font font = getFontByExcel(cell.getCellStyle(),true);
					pdfpCell.setPhrase(new Phrase(getStringValue(cell, evaluator,cale,prevCellVal),font));
				}
				// pdfpCell.setFixedHeight(this.getPixelHeight(row.getHeightInPoints()));
				pdfpCell.setMinimumHeight(this.getPixelHeight(row.getHeightInPoints()));
				// todo 不自動換行,應該參數化
				// pdfpCell.setNoWrap(true);
				pdfpCell.setNoWrap(false);
				// 中文字才不會黏在邊界線上
				pdfpCell.setPaddingBottom(3);

				addBorderByExcel(pdfpCell, cell.getCellStyle());
				addImageByPOICell(pdfpCell, cell, cw);
				//
				cells.add(pdfpCell);
				j += colspan - 1;
				if(j > 0 && sheet.getSheetName().indexOf("01表") != -1 && i < 12) {
					prevCellVal = getStringValue(cell,evaluator,null,null);
				}
			}
			// 若 Column 數不足(POI 產生的 Excel 可能有此狀況),可能導致下一行的 Cell 跑去上一列尾端
			int emptyColumns = columnCount - colCount;
			if (emptyColumns > 0) {
				PdfPCell pdfpCell = new PdfPCell();
				pdfpCell.setColspan(emptyColumns);
				pdfpCell.setRowspan(1);
				cells.add(pdfpCell);
			}
			float rw = 0;
			for (int j = 0; j < cws.length; j++) {
				rw += cws[j];
			}
			if (rw > mw || mw == 0) {
				widths = cws;
				mw = rw;
			}
		}

		PdfPTable table = new PdfPTable(columnCount);
		// 設定表格的欄位寬度
		float[] colWidths = new float[columnCount];
		for (int i = 0; i < columnCount; i++)
			colWidths[i] = columnWidths.get(i);
		table.setWidths(colWidths);
		table.setWidthPercentage(100);
		// table.setLockedWidth(true);
		for (PdfPCell pdfpCell : cells) {
			table.addCell(pdfpCell);
		}
		return table;
	}

	public static String getStringValue(Cell cell, FormulaEvaluator evaluator,Integer cale,String prevCellValue) {
		String cellVal = "";
		if (cell == null) {
			return cellVal;
		}
		switch (cell.getCellType().getCode()) {
		case 0:// 数字类型
			if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
				SimpleDateFormat sdf = null;
				if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
					sdf = new SimpleDateFormat("HH:mm");
				} else {// 日期
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				}
				Date date = cell.getDateCellValue();
				cellVal = sdf.format(date);
			} else if (cell.getCellStyle().getDataFormat() == 58) {
				// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				double value = cell.getNumericCellValue();
				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
				cellVal = sdf.format(date);
			} else {
				Double value = cell.getNumericCellValue();
				cellVal = getStringValByFomualOrNumber(cale, prevCellValue, value);
			}
			break;
		case 1:// String类型
			cellVal = cell.getRichStringCellValue().toString();
			break;

		case 3: // 本就是空
			cellVal = "";
		case 2: // 带函数的数据
		default:
			try {
				cellVal = "";
				CellValue cellValue = evaluator.evaluate(cell);
				if (cellValue == null) {
					cellVal = cell.getRichStringCellValue().toString();
				} else {
					if (cellValue.getCellType().getCode() == 0) {
						cellVal = "" + cellValue.getNumberValue();

					} else {
						cellVal = cellValue.getStringValue();
					}
				}
			} catch (Exception e) {
				try {
					cellVal = "" + cell.getStringCellValue();
				} catch (Exception e2) {
					try {
						cellVal = String.valueOf(cell.getNumericCellValue());   
					} catch(Exception e3) {
						cellVal = "";
					}
				}
			}
			cellVal = getStringValByFomualOrNumber(cale,prevCellValue,cellVal);
			break;

		}
		return cellVal == null ? "" : cellVal.trim();
	}

	/**
	 * 通过单元格的算式或者数值返回需要的精度
	 * <p>Title: getStringValByFomualOrNumber</p>  
	 * <p>Description: </p>  
	 * @param cell
	 * @param cale
	 * @param prevCellValue
	 * @param value
	 * @return
	 */
	private static String getStringValByFomualOrNumber(Integer cale, String prevCellValue, Object value) {
		String cellVal;
		DecimalFormat format = new DecimalFormat();
		int defalutCale = 2;//默认三位小数
		if(cale != null && cale >= 0) {
			defalutCale = cale;
		}
		if(prevCellValue != null) {
			if(prevCellValue.indexOf("长") != -1) {
				defalutCale = 3;
			}
			if(prevCellValue.indexOf("宽") != -1 || prevCellValue.indexOf("宽") != -1) {
				defalutCale = 2;
			}
		}
		String caleString = "";
		for(int i = 0;i < defalutCale;i++) {
			caleString += "0";
		}
		if(caleString.equals("")) {
			//保留整数
			format.applyPattern("0");//默认三位小数
		}else {
			try {
				// 单元格设置成常规
				format.applyPattern("#."+caleString+"");//默认三位小数
			} catch (Exception e) {
				format.applyPattern("0."+caleString);// 推荐
			}
		}
		try {
			String numString = value.toString();
			if (value.toString().indexOf("E") != -1) {
				BigDecimal num = new BigDecimal(value.toString());
				if (num != null) {
					if (num.compareTo(BigDecimal.ZERO) == 0) {
						numString = "0";
					} else {
						numString = num.stripTrailingZeros().toPlainString();
					}
				}
			}
			cellVal = format.format(Double.valueOf(numString));
		} catch (Exception e) {
			cellVal = value.toString();
		}
		if (cellVal.indexOf("E") != -1) {
			BigDecimal num = new BigDecimal(cellVal);
			if (num != null) {
				if (num.compareTo(BigDecimal.ZERO) == 0) {
					cellVal = "0";
				} else {
					cellVal = num.stripTrailingZeros().toPlainString();
				}
			}
		} else {

		}

		if (cellVal.contains(",")) {
			cellVal = cellVal.replaceAll(",", "");
		}
		cellVal = replaceBlank(cellVal);
		if (cellVal.contains(".")) {

			String[] xxx = cellVal.split("\\.");
			if (xxx[0].equals("")) {
				cellVal = "0" + cellVal;
			}
			String xx = xxx[1];
			try {
				if (Integer.parseInt(xx) == 0) {
					cellVal = xxx[0];
					if (cellVal.trim().equals("")) {
						cellVal = "0";
					}
				}
			} catch (Exception e) {
			}
		}
		return cellVal;
	}

	public static String replaceBlank(String str) {
		String dest = "";
		if (str != null) {
			Pattern p = Pattern.compile(" |　|\\s*|\t|\r|\n");
			Matcher m = p.matcher(str);
			dest = m.replaceAll("");
		}
		return dest;
	}

	protected Phrase getPhrase(Cell cell,Integer cale,String prevCellValue) {
		FormulaEvaluator evaluator = this.excelObject.getWorkbook().getCreationHelper().createFormulaEvaluator();
		if (this.setting || this.excelObject.getAnchorName() == null) {
			//项目有要求，要保持精度，所以这里在定义的getStringCellValue()方法中处理，如果不要保留自定义精度，那么把注释放开即可
//			return new Phrase(cell.getStringCellValue(), getFontByExcel(cell.getCellStyle(),null));
			return new Phrase(getStringValue(cell,evaluator,cale,prevCellValue), getFontByExcel(cell.getCellStyle(),null));
		}
		//项目有要求，要保持精度，所以这里在定义的getStringCellValue()方法中处理
//		Anchor anchor = new Anchor(cell.getStringCellValue(), getFontByExcel(cell.getCellStyle(),null));
		Anchor anchor = new Anchor(getStringValue(cell,evaluator,cale,prevCellValue), getFontByExcel(cell.getCellStyle(),null));
		anchor.setName(this.excelObject.getAnchorName());
		this.setting = true;
		return anchor;
	}
	
	protected Phrase getPhrase(Cell cell) {
		if (this.setting || this.excelObject.getAnchorName() == null) {
			return new Phrase(cell.getStringCellValue(), getFontByExcel(cell.getCellStyle(),null));
		}
		Anchor anchor = new Anchor(cell.getStringCellValue(), getFontByExcel(cell.getCellStyle(),null));
		anchor.setName(this.excelObject.getAnchorName());
		this.setting = true;
		return anchor;
	}

	protected void addImageByPOICell(PdfPCell pdfpCell, Cell cell, float cellWidth)
			throws BadElementException, MalformedURLException, IOException {
		POIImage poiImage = new POIImage().getCellImage(cell);
		byte[] bytes = poiImage.getBytes();
		if (bytes != null) {
			// double cw = cellWidth;
			// double ch = pdfpCell.getFixedHeight();
			//
			// double iw = poiImage.getDimension().getWidth();
			// double ih = poiImage.getDimension().getHeight();
			//
			// double scale = cw / ch;
			//
			// double nw = iw * scale;
			// double nh = ih - (iw - nw);
			//
			// POIUtil.scale(bytes , nw , nh);
			pdfpCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
			pdfpCell.setHorizontalAlignment(Element.ALIGN_CENTER);
			Image image = Image.getInstance(bytes);
			pdfpCell.setImage(image);
		}
	}

	protected float getPixelHeight(float poiHeight) {
		float pixel = poiHeight / 28.6f * 26f;
		return pixel;
	}

	/**
	 * <p>
	 * Description: 此处获取Excel的列宽像素(无法精确实现,期待有能力的朋友进行改善此处)
	 * </p>
	 * 
	 * @param cell
	 * @return 像素宽
	 */
	protected int getPOIColumnWidth(Cell cell) {
		int poiCWidth = excel.getSheet().getColumnWidth(cell.getColumnIndex());
		int colWidthpoi = poiCWidth;
		int widthPixel = 0;
		if (colWidthpoi >= 416) {
			widthPixel = (int) (((colWidthpoi - 416.0) / 256.0) * 8.0 + 13.0 + 0.5);
		} else {
			widthPixel = (int) (colWidthpoi / 416.0 * 13.0 + 0.5);
		}
		return widthPixel;
	}

	protected CellRangeAddress getColspanRowspanByExcel(int rowIndex, int colIndex) {
		CellRangeAddress result = null;
		Sheet sheet = excel.getSheet();
		int num = sheet.getNumMergedRegions();
		for (int i = 0; i < num; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			if (range.getFirstColumn() == colIndex && range.getFirstRow() == rowIndex) {
				result = range;
			}
		}
		return result;
	}

	protected boolean isUsed(int colIndex, int rowIndex) {
		boolean result = false;
		Sheet sheet = excel.getSheet();
		int num = sheet.getNumMergedRegions();
		for (int i = 0; i < num; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			if (firstRow < rowIndex && lastRow >= rowIndex) {
				if (firstColumn <= colIndex && lastColumn >= colIndex) {
					result = true;
				}
			}
		}
		return result;
	}

	protected Font getFontByExcel(CellStyle style,Boolean isDefault) {
		Font result = new Font(Resource.BASE_FONT_CHINESE, 8, Font.NORMAL);
		Workbook wb = excel.getWorkbook();
		short index = style.getFontIndex();
		org.apache.poi.ss.usermodel.Font font = wb.getFontAt(index);
		
		/*if(isDefault != null && isDefault) {
			result.setStyle(Font.BOLD);
		}*/
		
		/*if (font.getBold()) {
			result.setStyle(Font.BOLD);
		}*/
		
		HSSFColor color = HSSFColor.getIndexHash().get(font.getColor());
		
		if (color != null) {
			int rbg = POIUtil.getRGB(color);
			result.setColor(new BaseColor(rbg));
		}
		
		FontUnderline underline = FontUnderline.valueOf(font.getUnderline());
		if (underline == FontUnderline.SINGLE) {
			String ulString = Font.FontStyle.UNDERLINE.getValue();
			result.setStyle(ulString);
		}
		return result;
	}

	protected int[] getBackgroundColor(CellStyle style) {
		Color color = style.getFillForegroundColorColor();
		return POIUtil.getColorRGB(color);
	}

	protected int getBackgroundColorByExcel(CellStyle style) {
		Color color = style.getFillForegroundColorColor();
		return POIUtil.getRGB(color);
	}

	/**
	 * TODO:如何將 Excel 中不同樣式粗細的邊線在 PDF 中呈現
	 * 
	 * @param cell
	 * @param style
	 */
	protected void addBorderByExcel(PdfPCell cell, CellStyle style) {
		Workbook wb = excel.getWorkbook();
		cell.setBorderColorLeft(new BaseColor(POIUtil.getBorderRBG(wb, style.getLeftBorderColor())));
		cell.setBorderColorRight(new BaseColor(POIUtil.getBorderRBG(wb, style.getRightBorderColor())));
		cell.setBorderColorTop(new BaseColor(POIUtil.getBorderRBG(wb, style.getTopBorderColor())));
		cell.setBorderColorBottom(new BaseColor(POIUtil.getBorderRBG(wb, style.getBottomBorderColor())));
		// 設定四邊是否有邊線
		short left = getBorderWidth(style.getBorderLeft().getCode());
		short right = getBorderWidth(style.getBorderRight().getCode());
		short top = getBorderWidth(style.getBorderTop().getCode());
		short bottom = getBorderWidth(style.getBorderBottom().getCode());
		if (left > 0)
			cell.enableBorderSide(PdfPCell.LEFT);
		else
			cell.disableBorderSide(PdfPCell.LEFT);
		if (right > 0)
			cell.enableBorderSide(PdfPCell.RIGHT);
		else
			cell.disableBorderSide(PdfPCell.RIGHT);
		if (top > 0)
			cell.enableBorderSide(PdfPCell.TOP);
		else
			cell.disableBorderSide(PdfPCell.TOP);
		if (bottom > 0)
			cell.enableBorderSide(PdfPCell.BOTTOM);
		else
			cell.disableBorderSide(PdfPCell.BOTTOM);
	}

	protected short getBorderWidth(short borderType) {
//		switch (borderType) {
//		case CellStyle.BORDER_DASH_DOT:
//		case CellStyle.BORDER_DASH_DOT_DOT:
//		case CellStyle.BORDER_DASHED:
//		case CellStyle.BORDER_DOTTED:
//		case CellStyle.BORDER_HAIR:
//		case CellStyle.BORDER_DOUBLE:
//		case CellStyle.BORDER_MEDIUM:
//		case CellStyle.BORDER_MEDIUM_DASH_DOT:
//		case CellStyle.BORDER_MEDIUM_DASH_DOT_DOT:
//		case CellStyle.BORDER_MEDIUM_DASHED:
//		case CellStyle.BORDER_SLANTED_DASH_DOT:
//			return 1;
//		case CellStyle.BORDER_NONE:
//			return 0;
//		case CellStyle.BORDER_THIN:
//			return 1;
//		case CellStyle.BORDER_THICK:
//			return 2;
//		default:
//			return 1;
//		}
		return 1;
	}

	protected int getVAlignByExcel(short align) {
//		int result = 0;
//		if (align == CellStyle.VERTICAL_BOTTOM) {
//			result = Element.ALIGN_BOTTOM;
//		}
//		if (align == CellStyle.VERTICAL_CENTER) {
//			result = Element.ALIGN_MIDDLE;
//		}
//		if (align == CellStyle.VERTICAL_JUSTIFY) {
//			result = Element.ALIGN_JUSTIFIED;
//		}
//		if (align == CellStyle.VERTICAL_TOP) {
//			result = Element.ALIGN_TOP;
//		}
//		return result;
		return Element.ALIGN_MIDDLE;
	}

	protected int getHAlignByExcel(short align) {
//		int result = 0;
//		if (align == CellStyle.ALIGN_LEFT) {
//			result = Element.ALIGN_LEFT;
//		}
//		if (align == CellStyle.ALIGN_RIGHT) {
//			result = Element.ALIGN_RIGHT;
//		}
//		if (align == CellStyle.ALIGN_JUSTIFY) {
//			result = Element.ALIGN_JUSTIFIED;
//		}
//		if (align == CellStyle.ALIGN_CENTER) {
//			result = Element.ALIGN_CENTER;
//		}
//		return result;
		return Element.ALIGN_CENTER;
	}
}